import os
import xlwt,xlrd
from xlutils.copy import copy

class ReadDataFromExcel:
    def __init__(self,filepath):
        self.filename = filepath
        self.open_excel()

    def open_excel(self):
        self.workbook = xlrd.open_workbook(self.filename)

    def read_all_sheets_content(self):
        self.sheets = self.workbook.sheet_names()                              #返回所有sheet的列表
        print(self.sheets)
        for i in self.sheets:
            self.mysheet = self.workbook.sheet_by_name(i)                      #通过sheet name获取指定的sheet
            for j in range(self.mysheet.nrows):                                #获取当前sheet的总行数
                for k in range(self.mysheet.ncols):                            #获取当前sheet的总列数
                    print('%ssheet%d行%d列的内容为:' % (i, j + 1, k + 1), self.mysheet.cell_value(j, k))       #获取指定单元格的内容

    def get_sheet_by_name(self,sheetname):
        try:
            self.mysheetbook = self.workbook.sheet_by_name(sheetname)         #通过sheet name 获取sheet
        except Exception as e:
            msg = '%s不存在:'%sheetname + str(e)
            raise  Exception(msg)
        return self.mysheetbook

    def get_sheet_by_index(self,index):
        try:
            self.mysheetbook = self.workbook.sheet_by_index(index)            #通过index获取sheet
        except Exception as e:
            msg = '%d超出index范围:'%index + str(e)
            raise  Exception(msg)
        return self.mysheetbook

    def read_sheet_content_row(self,sheetname,row):
            self.mysheet = self.get_sheet_by_name(sheetname)
            try:
                self.values = self.mysheet.row_values(row)                   #通过index读取指定行的内容，index从0开始
            except Exception as e:
                raise Exception('%d超出了index范围'%row)
            else:
                return self.values

    def read_sheet_content_col(self,sheetname,col):
        self.mysheet = self.get_sheet_by_name(sheetname)
        try:
            self.values = self.mysheet.col_values(col)           #通过index读取指定行的内容，index从0开始
        except Exception as e:
            raise Exception('%d超出了index范围'%col)
        else:
            return self.values

    def read_sheet_content_rows(self,sheetname):
        self.mysheet = self.get_sheet_by_name(sheetname)
        self.values = self.mysheet.get_rows()          #以行的形式读取所有内容，返回的是一个二维list
        print(len(self.values))
        return self.values

    def read_sheet_cell_content(self,sheetname,r,c):
        '''
        获取指定单元格的内容
        1、 self.mysheet.cell_value(r,c)
        2、self.mysheet.row(r)[c].value
        3、self.mysheet.cell(r,c).value
        '''
        self.mysheet = self.get_sheet_by_name(sheetname)
        try:
            self.value = self.mysheet.cell_value(r,c)
        except Exception as e:
            raise Exception('输入的%d行%d列可能不存在'%(r,c))
        else:
            return self.value

    def read_first_row_content(self,sheetname):
        mysheet = self.get_sheet_by_name(sheetname)
        headers = []
        for k in range(mysheet.ncols):
            headers.append(mysheet.cell_value(0, k))
        return headers

    def read_data_to_dict(self,sheetname):
        values = []
        mysheet = self.get_sheet_by_name(sheetname)
        headers = self.read_first_row_content(sheetname)
        for j in range(1,mysheet.nrows):  # 获取当前sheet的总行数
            rvalues = {}
            for k in range(mysheet.ncols):  # 获取当前sheet的总列数
                s = {}
                if headers[k] =='url' or headers[k] =='项目' or headers[k] =='模块' or headers[k] =='method':       #如果是url列，则不需要将内容转化为字典
                    rvalues[headers[k]] = mysheet.cell_value(j, k)
                elif headers[k] =='data' or headers[k] =='check':
                    content = str(mysheet.cell_value(j, k))
                    contents = content.split(',')     #将单元格的内容按照,进行分割
                    for c in contents:
                        vs = str(c).split('=')          #将按照,分割的内容再按照=进行分割
                        s[vs[0]] = vs[1]                #等好前的内容作为key,后面的内容作为value存储
                    rvalues[headers[k]] = s
                else:
                    pass
            values.append(rvalues)
        return values

class WritDataInExcel:
    def __init__(self,filename,sname):
        self.filename = filename
        self.sname = sname
        self.open_excel(self.sname)

    def open_excel(self,sname):
        self.book = xlwt.Workbook()
        self.mys = self.book.add_sheet(sname)

    def write_data(self,r,c,value):
        self.mys.write(r,c,value)        #向单元格写入内容
        '''
        self.mys.write(r,c,xlwt.Formula("A3+B3"))      #向单元格中写入公式
        '''

    def save_data(self):
        self.book.save(self.filename)

class WriteContentInOldBook():

    def __init__(self,filename,sheet_index):
        self.filepath = os.path.dirname(os.path.dirname(__file__)) + '/file/' + filename
        # self.filename = filename
        old_wb = xlrd.open_workbook(self.filepath)
        self.new_wb = copy(old_wb)
        self.new_sheet = self.new_wb.get_sheet(sheet_index)

    def write_content(self,r,c,content):
        self.new_sheet.write(r,c,content)

    def save_data(self):
        self.new_wb.save(self.filepath)

class GetTestData():

    def __init__(self,filename,sheetname):
        filepath = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'file', filename)
        print(filepath)
        rm = ReadDataFromExcel(filepath)
        self.result = rm.read_data_to_dict(sheetname)

    def get_data(self):
        datas = []
        for r in self.result:
            data = []
            data.append(r['url'])
            data.append(r['method'])
            data.append(r['data'])
            data.append(r['check'])
            datas.append(data)
        return datas

if __name__ == '__main__':
    filepath = os.path.dirname(os.path.dirname(__file__)) + '/file/' + 'user.xlsx'
    # print(filepath)
    d = WriteContentInOldBook(filepath,'Sheet1')
    d.write_content(1,7,'内容')
    d.save_data()
